/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.sdkj.dspservice.modules.analysis.dao;

import com.sdkj.dspservice.common.base.dao.BaseDao;
import com.sdkj.dspservice.modules.analysis.entity.PageParamAnalysis;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * url统计DAO接口
 * @author tm
 */
public interface PageParamAnalysisDao extends BaseDao<PageParamAnalysis> {

    /**
     * 查询总数
     * @param pageParamAnalysis
     * @return
     */
    @Select("select count(*) from (select\n" +
            "    concat(domain,attributes['$path']) as url,\n" +
            "    count(distinct user) as uv,\n" +
            "    count(1) as pv\n" +
            "from event_all\n" +
            "where toYYYYMMDD(event_time) between #{startDate}\n" +
            "    and #{endDate}\n" +
            "  and event_type = 'page'\n" +
            "  and domain = domain(#{url})\n" +
            "  and match(attributes['$path'],path(#{url})) = 1\n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "group by url)")
    Integer count(PageParamAnalysis pageParamAnalysis);

    /**
     * 获取列表
     * @param pageParamAnalysis
     * @return
     */
    @Select("select\n" +
            "    concat(domain,attributes['$path']) as url,\n" +
            "    attributes['$title'] as title,\n" +
            "    count(distinct user) as uv,\n" +
            "    count(1) as pv\n" +
            "from event_all\n" +
            "where toYYYYMMDD(event_time) between #{startDate}\n" +
            "    and #{endDate}\n" +
            "  and event_type = 'page'\n" +
            "  and domain = domain(#{url})\n" +
            "  and match(attributes['$path'],path(#{url})) = 1\n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "group by url,title order by uv desc limit #{limitLeft},#{size}")
    List<PageParamAnalysis> list(PageParamAnalysis pageParamAnalysis);

    /**
     * 获取地市
     * @param pageParamAnalysis
     * @return
     */
    @Select("select\n" +
            "    case when n.prov_name ='江西' then n.city_name else '其他' end as cityName,\n" +
            "    count(distinct m.user) as uv,sum(m.pv) as pv from\n" +
            "    (select concat(domain,attributes['$path']) as url,user,count(1) as pv from event_all\n" +
            "     where toYYYYMMDD(event_time) between #{startDate} and #{endDate}\n" +
            "       and event_type = 'page'\n" +
            "       and domain = domain(#{url})\n" +
            "       and attributes['$path'] = path(#{url})\n" +
            "       and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "     group by url,user )m\n" +
            "        left join\n" +
            "    (select hlr_code,city_name,prov_name from gio.haoduan_use)n on substring(m.user,1,7)=n.hlr_code group by cityName")
    List<PageParamAnalysis> listCity(PageParamAnalysis pageParamAnalysis);

    /**
     * 获取子节点
     * @param pageParamAnalysis
     * @return
     */
    @Select("select\n" +
            "    case when attributes['$query'] <> ''\n" +
            "             then concat(domain,attributes['$path'],'?',attributes['$query'])\n" +
            "         else concat(domain,attributes['$path'])\n" +
            "        end\n" +
            "                         as url,\n" +
            "    attributes['$title'] as title,\n" +
            "    count(distinct user) as uv,\n" +
            "    count(1) as pv\n" +
            " from event_all\n" +
            " where toYYYYMMDD(event_time) between #{startDate}\n" +
            "    and #{endDate}\n" +
            "  and event_type = 'page'\n" +
            "  and domain = domain(#{url})\n" +
            "  and match(attributes['$path'],path(#{url})) = 1\n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "group by url,title order by uv desc limit 10")
    List<PageParamAnalysis> listChildren(PageParamAnalysis pageParamAnalysis);

    /**
     * 获取子节点地市
     * @param pageParamAnalysis
     * @return
     */
    @Select("select\n" +
            "    case when n.prov_name ='江西' then n.city_name else '其他' end as city_name,\n" +
            "    count(distinct m.user) as uv,sum(m.pv) as pv from\n" +
            "    (select\n" +
            "         case when attributes['$query'] <> ''\n" +
            "                  then concat(domain,attributes['$path'],'?',attributes['$query'])\n" +
            "              else concat(domain,attributes['$path'])\n" +
            "             end  as url2,\n" +
            "         user,\n" +
            "         count(1) as pv\n" +
            "     from event_all\n" +
            "     where toYYYYMMDD(event_time) between #{startDate}\n" +
            "         and #{endDate}\n" +
            "       and event_type = 'page'\n" +
            "       and domain = domain(#{url})\n" +
            "       and attributes['$path']=path(#{url})\n" +
            "       and attributes['$query'] = queryString(#{url})\n" +
            "       and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "     group by url2,user)m\n" +
            "        left join\n" +
            "    (select hlr_code,city_name,prov_name from gio.haoduan_use)n on substring(m.user,1,7)=n.hlr_code group by city_name")
    List<PageParamAnalysis> listChildCity(PageParamAnalysis pageParamAnalysis);

    /**
     * 获取访前
     * @param pageParamAnalysis
     * @return
     */
    @Select("select case when attributes['$referrer'] <> ''\n" +
            "                then cutQueryString(attributes['$referrer'])\n" +
            "            else '直接访问'\n" +
            "           end as url,\n" +
            "       count(distinct user) as uv,\n" +
            "       count(1) as pv\n" +
            "from event_all\n" +
            "where toYYYYMMDD(event_time) between #{startDate}\n" +
            "    and #{endDate}\n" +
            "  and event_type = 'page'\n" +
            "  and domain = domain(#{url})\n" +
            "  and attributes['$path']=path(#{url})\n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "group by url order by uv desc limit 10")
    List<PageParamAnalysis> listVisitBefore(PageParamAnalysis pageParamAnalysis);

    /**
     * 获取访前地市
     * @param pageParamAnalysis
     * @return
     */
    @Select("select a.city_name,count(distinct a.user) as uv,sum(a.pv) as pv from (\n" +
            "                                                                   select m.referrer,\n" +
            "                                                                          case when n.prov_name ='江西' then n.city_name else '其他' end as city_name,\n" +
            "                                                                          m.user,m.pv from\n" +
            "                                                                       (select case when attributes['$referrer'] <> ''\n" +
            "                                                                                        then cutQueryString(attributes['$referrer'])\n" +
            "                                                                                    else '直接访问'\n" +
            "                                                                                   end as referrer,\n" +
            "                                                                               user,\n" +
            "                                                                               count(1) as pv\n" +
            "                                                                        from event_all\n" +
            "                                                                        where toYYYYMMDD(event_time) between #{startDate}\n" +
            "         and #{endDate}\n" +
            "                                                                          and event_type = 'page'\n" +
            "                                                                          and domain = domain(#{parentUrl})\n" +
            "                                                                          and attributes['$path']=path(#{parentUrl})\n" +
            "                                                                          and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "                                                                        group by referrer,user)m\n" +
            "                                                                           left join\n" +
            "                                                                       (select hlr_code,city_name,prov_name from gio.haoduan_use)n on substring(m.user,1,7)=n.hlr_code )a\n" +
            "where a.referrer=#{url} group by city_name")
    List<PageParamAnalysis> listVisitBeforeCity(PageParamAnalysis pageParamAnalysis);
    
    /**
     * 获取所有url
     */
    @Select("select\n" +
            "    distinct concat(domain,attributes['$path']) as url\n" +
            "from event_all\n" +
            "where toYYYYMMDD(event_time) <= toYYYYMMDD(now())\n" +
            "  and event_type = 'page'\n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "group by url\n" +
            "order by count(distinct user) desc;")
    List<String> listUrl();

    /**
     * 直接显示同级11条
     * @param pageParamAnalysis
     * @return
     */
    @Select("SELECT\n" +
            "CASE\n" +
            "\tWHEN attributes\n" +
            "\t\t[ '$query' ] <> '' THEN\n" +
            "\t\t\tconcat ( domain, attributes [ '$path' ], '?', attributes [ '$query' ] ) ELSE concat ( domain , attributes [ '$path' ] ) \n" +
            "\t\t\tEND AS url,\n" +
            "\t\tCOUNT ( DISTINCT user ) AS uv,\n" +
            "\t\tCOUNT ( 1 ) AS pv \n" +
            "\tFROM\n" +
            "\t\tevent_all \n" +
            "\tWHERE\n" +
            "\t\ttoYYYYMMDD ( event_time ) BETWEEN #{ startDate } \n" +
            "\t\tAND #{ endDate } \n" +
            "\t\tAND event_type = 'page' \n" +
            "\t\tAND domain  = domain ( #{ url } ) \n" +
            "\t\tAND match ( attributes [ '$path' ], path ( #{ url } ) ) = 1 \n" +
            "\t\tAND match ( user, '^1[3-9]\\\\d{9}$' ) = 1 \n" +
            "\tGROUP BY\n" +
            "\t\turl \n" +
            "\tORDER BY\n" +
            "\t\tuv DESC \n" +
            "\t\tLIMIT 10 UNION ALL\n" +
            "\tSELECT\n" +
            "\t\tconcat ( domain, attributes [ '$path' ] ) AS url,\n" +
            "\t\tCOUNT ( DISTINCT user ) AS uv,\n" +
            "\t\tCOUNT ( 1 ) AS pv \n" +
            "\tFROM\n" +
            "\t\tevent_all \n" +
            "\tWHERE\n" +
            "\t\ttoYYYYMMDD ( event_time ) BETWEEN #{ startDate } \n" +
            "\t\tAND #{ endDate } \n" +
            "\t\tAND event_type = 'page' \n" +
            "\t\tAND domain  = domain ( #{ url } ) \n" +
            "\t\tAND attributes [ '$path' ]= path ( #{ url } ) \n" +
            "\t\tAND attributes [ '$query' ] = queryString ( #{ url } ) \n" +
            "\t\tAND match ( user, '^1[3-9]\\\\d{9}$' ) = 1 \n" +
            "GROUP BY\n" +
            "\turl")
    List<PageParamAnalysis> listTotal2(PageParamAnalysis pageParamAnalysis);

}
